﻿using EASEater.Models;
using Oracle.ManagedDataAccess.Client;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Windows;

namespace EASEater.Help
{
    public static class Data
    {
        public static string SqlServer { get; set; }

        public static string Oracle { get; set; }

        static Data()
        {
        SqlServer = "";
            Oracle = "";
        }

        public static List<Map> OfficialTables { get; set; }

        public static List<Map> OfficialFields { get; set; }

        public static List<Map> ExcelTables { get; set; }

        public static List<Map> ExcelFields { get; set; }

        public static List<Map> JoinOfficialTables { get; set; }

        public static List<Map> JoinOfficialFields { get; set; }

        public static void LoadOfficial()
        {
            try
            {
                using (OracleConnection c = new OracleConnection(Oracle))
                {
                    c.Open();
                    using (OracleCommand cmd = new OracleCommand("SELECT distinct tablename, tablenamech FROM DATAFIELDLIST order by tablenamech", c))
                    {
                        using (OracleDataReader r = cmd.ExecuteReader())
                        {
                            OfficialTables = new List<Map>();
                            while (r.Read())
                            {
                                OfficialTables.Add(new Map
                                {
                                    DbName = r.IsDBNull(0) ? string.Empty : r.GetString(0),
                                    BillName = r.IsDBNull(1) ? string.Empty : r.GetString(1)
                                });
                            }
                        }
                    }
                    using (OracleCommand cmd = new OracleCommand("SELECT distinct tablename, fieldname, fieldnamech FROM DATAFIELDLIST order by fieldnamech", c))
                    {
                        using (OracleDataReader r = cmd.ExecuteReader())
                        {
                            OfficialFields = new List<Map>();
                            while (r.Read())
                            {
                                OfficialFields.Add(new Map
                                {
                                    DbName = r.IsDBNull(1) ? string.Empty : r.GetString(1),
                                    BillName = r.IsDBNull(2) ? string.Empty : r.GetString(2),
                                    Parent = r.IsDBNull(0) ? string.Empty : r.GetString(0)
                                });
                            }
                        }
                    }
                }
            }
            catch (System.Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        public static void LoadExcel()
        {
            try
            {
                using (SqlConnection c = new SqlConnection(SqlServer))
                {
                    c.Open();
                    using (SqlCommand cmd = new SqlCommand("SELECT DISTINCT [TABLENAME] ,[TABLEDESC] FROM [DATASETS]", c))
                    {
                        using (SqlDataReader r = cmd.ExecuteReader())
                        {
                            ExcelTables = new List<Map>();
                            while (r.Read())
                            {
                                ExcelTables.Add(new Map
                                {
                                    DbName = r.IsDBNull(0) ? string.Empty : r.GetString(0),
                                    BillName = r.IsDBNull(1) ? string.Empty : r.GetString(1)
                                });
                            }
                        }
                    }

                    using (SqlCommand cmd = new SqlCommand("SELECT [TABLENAME],[FIELDNAME],[FIELDDESC] FROM [DATASETS]", c))
                    {
                        using (SqlDataReader r = cmd.ExecuteReader())
                        {
                            ExcelFields = new List<Map>();
                            while (r.Read())
                            {
                                ExcelFields.Add(new Map
                                {
                                    DbName = r.IsDBNull(1) ? string.Empty : r.GetString(1),
                                    BillName = r.IsDBNull(2) ? string.Empty : r.GetString(2),
                                    Parent = r.IsDBNull(0) ? string.Empty : r.GetString(0)
                                });
                            }
                        }
                    }
                }
            }
            catch (System.Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        public static void LoadJoinOfficial()
        {
            try
            {
                using (OracleConnection c = new OracleConnection(Oracle))
                {
                    c.Open();
                    string sql =
    @"select Table_name, b.tablenamech
from user_tables a 
left join (
     select distinct TABLENAME, TABLENAMECH from datafieldlist
     ) b on a.TABLE_NAME = upper(b.tablename)";
                    using (OracleCommand cmd = new OracleCommand(sql, c))
                    {
                        using (OracleDataReader r = cmd.ExecuteReader())
                        {
                            JoinOfficialTables = new List<Map>();
                            while (r.Read())
                            {
                                JoinOfficialTables.Add(new Map
                                {
                                    DbName = r.IsDBNull(0) ? string.Empty : r.GetString(0),
                                    BillName = r.IsDBNull(1) ? string.Empty : r.GetString(1)
                                });
                            }
                        }
                    }
                    sql =
    @"select a.TABLE_NAME, a.COLUMN_NAME, b.fieldnamech
from user_tab_columns a
left join (
     select tablename, fieldname, fieldnamech from datafieldlist
     ) b on a.TABLE_NAME = upper(b.tablename) and a.COLUMN_NAME = upper(b.fieldname)";
                    using (OracleCommand cmd = new OracleCommand(sql, c))
                    {
                        using (OracleDataReader r = cmd.ExecuteReader())
                        {
                            JoinOfficialFields = new List<Map>();
                            while (r.Read())
                            {
                                JoinOfficialFields.Add(new Map
                                {
                                    DbName = r.IsDBNull(1) ? string.Empty : r.GetString(1),
                                    BillName = r.IsDBNull(2) ? string.Empty : r.GetString(2),
                                    Parent = r.IsDBNull(0) ? string.Empty : r.GetString(0)
                                });
                            }
                        }
                    }
                }
            }
            catch (System.Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }
    }
}


